console.log('Starting dbConfig.js');

const path = require('path');
const fs = require('fs');

try {
  console.log('正在加载 better-sqlite3');
  const Database = require('better-sqlite3');

  // 动态获取 myapp.exe 所在目录
  const isPackaged = process.execPath.endsWith('SKSystem.exe');
  const exeDir = isPackaged ? path.dirname(process.execPath) : path.join(__dirname, '../');
  console.log('执行文件所在路径:', exeDir);

  // 本地数据库路径（可写）
  const localDbDir = path.join(exeDir, 'data');
  const localDbPath = path.join(localDbDir, 'SKData.db');
  // 嵌入的数据库虚拟路径
  const embeddedDbPath = path.join(__dirname, '../data/SKData.db');
  console.log('嵌入数据库路径:', embeddedDbPath, '本地数据库路径:', localDbPath);

  // 确保本地 data 目录存在
  if (!fs.existsSync(localDbDir)) {
    console.log('创建本地数据库存储目录:', localDbDir);
    fs.mkdirSync(localDbDir, { recursive: true });
  }

  // 如果本地数据库不存在，创建空数据库
  if (!fs.existsSync(localDbPath)) {
    console.log('创建空数据库文件:', localDbPath);
    const tempDb = new Database(localDbPath);
    tempDb.close();
  }

  // 创建数据库实例
  console.log('创建数据库实例:', localDbPath);
  let db;
  try {
    db = new Database(localDbPath, { verbose: console.log });
    console.log('数据库创建成功');
  } catch (err) {
    console.error('数据库创建失败:', err.stack);
    process.exit(1);
  }

  // 日志记录函数（写入 exe 所在目录）
  const log = (message) => {
    const logMessage = `${new Date().toISOString()} - ${message}\n`;
    console.log(logMessage);
    const logPath = path.join(exeDir, 'app.log');
    try {
      fs.appendFileSync(logPath, logMessage);
    } catch (err) {
      console.error('写入日志失败:', err.message);
    }
  };

  // 重试机制（用于处理 SQLITE_BUSY）
  const retryOperation = async (operation, maxRetries = 3) => {
    let attempts = 0;
    while (attempts < maxRetries) {
      try {
        return operation();
      } catch (err) {
        if (err.code === 'SQLITE_BUSY') {
          attempts++;
          log(`Retry attempt ${attempts} for SQLITE_BUSY`);
          await new Promise(resolve => setTimeout(resolve, 500 * attempts));
        } else {
          throw err;
        }
      }
    }
    throw new Error('超过最大重试次数');
  };

  // 模拟异步接口
  const runAsync = async (query, params = []) => {
    try {
      const stmt = db.prepare(query);
      const result = await retryOperation(() => stmt.run(...params));
      return result;
    } catch (err) {
      log(`Query failed: ${query}, error: ${err.message}`);
      throw err;
    }
  };

  const allAsync = async (query, params = []) => {
    try {
      const stmt = db.prepare(query);
      return await retryOperation(() => stmt.all(...params));
    } catch (err) {
      log(`Query failed: ${query}, error: ${err.message}`);
      throw err;
    }
  };

  const getAsync = async (query, params = []) => {
    try {
      const stmt = db.prepare(query);
      return await retryOperation(() => stmt.get(...params));
    } catch (err) {
      log(`Query failed: ${query}, error: ${err.message}`);
      throw err;
    }
  };

  // 初始化表结构
  async function initializeTables() {
    console.log('表初始化开始');
    const createTables = [
      `CREATE TABLE IF NOT EXISTS sk_ceremony_hall (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        partner_company_id INTEGER,
        partner_company_name VARCHAR(128),
        ceremony_hall_name VARCHAR(128),
        ceremony_hall_addr VARCHAR(2048),
        remarks TEXT,
        ceremony_hall_img VARCHAR(2048),
        del_flag CHAR(1) DEFAULT '0',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        create_user INTEGER,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_user INTEGER
      )`,
      `CREATE TABLE IF NOT EXISTS sk_partner_company (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        partner_company_name VARCHAR(128),
        is_wedding CHAR(1),
        partner_company_phone VARCHAR(128),
        partner_company_addr VARCHAR(2048),
        remarks TEXT,
        partner_company_img VARCHAR(2048),
        del_flag CHAR(1) DEFAULT '0',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        create_user INTEGER,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_user INTEGER
      )`,
      `CREATE TABLE IF NOT EXISTS sk_user_info (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_name VARCHAR(128),
        sex CHAR(1),
        phone VARCHAR(128) NOT NULL,
        wx_num VARCHAR(128),
        id_num VARCHAR(128),
        bank_num VARCHAR(128),
        occupation CHAR(1),
        stage_name VARCHAR(128),
        market_price INTEGER,
        collaboration_price INTEGER,
        is_internal CHAR(1),
        occupation_type CHAR(1),
        year_indicator INTEGER,
        introduction TEXT,
        portrait_photo VARCHAR(512),
        quotation VARCHAR(512),
        video VARCHAR(512),
        password VARCHAR(512) NOT NULL,
        sort_order INTEGER DEFAULT 0,
        del_flag CHAR(1) DEFAULT '0',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        create_user INTEGER,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_user INTEGER
      )`,
      `CREATE TABLE IF NOT EXISTS sk_collaborating_personnel (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        cooperation_name VARCHAR(128),
        nickname VARCHAR(128),
        cooperation_sex CHAR(1),
        partner_company_id INTEGER,
        partner_company_name VARCHAR(128),
        cooperation_phone VARCHAR(128),
        cooperation_wx_num VARCHAR(128),
        cooperation_id_num CHAR(1),
        remarks TEXT,
        individual_img VARCHAR(2048),
        del_flag CHAR(1) DEFAULT '0',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        create_user INTEGER,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_user INTEGER
      )`,
      `CREATE TABLE IF NOT EXISTS sk_schedule (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        schedule_type CHAR(1),
        schedule_date VARCHAR(128),
        company_type CHAR(1),
        partner_company_id INTEGER,
        company_name VARCHAR(128),
        hotel_name VARCHAR(128),
        ceremony_hall_id INTEGER,
        ceremony_hall_name VARCHAR(128),
        ceremony_hall_remarks VARCHAR(2048),
        fixed_gear_id INTEGER,
        fixed_gear_name VARCHAR(128),
        is_recommend CHAR(1),
        recommend_user VARCHAR(128),
        rebate INTEGER,
        service_price INTEGER,
        deposit INTEGER,
        studio_settlement INTEGER,
        profit INTEGER,
        wedding_is_checkout CHAR(1),
        studio_is_checkout CHAR(1),
        archive_img VARCHAR(1024),
        is_postponement CHAR(1),
        is_cancel CHAR(1),
        remarks TEXT,
        is_deposit CHAR(1),
        schedule CHAR(1),
        del_flag CHAR(1) DEFAULT '0',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        create_user INTEGER,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_user INTEGER
      )`,
      `CREATE TABLE IF NOT EXISTS sk_activity (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        activity_date VARCHAR(256),
        activity_theme VARCHAR(1024),
        activity_addr VARCHAR(1024),
        activity_content VARCHAR(2048),
        participate_users_id VARCHAR(1024),
        participate_users_name VARCHAR(5000),
        leave_users_id VARCHAR(1024),
        leave_users_name VARCHAR(5000),
        del_flag CHAR(1) DEFAULT '0',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        create_user INTEGER,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_user INTEGER
      )`,
      `CREATE TABLE IF NOT EXISTS sk_funds (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        funds_date VARCHAR(128),
        funds_user_id INTEGER,
        funds_user_name VARCHAR(128),
        funds_amount INTEGER,
        funds_reason VARCHAR(1025),
        activity_id INTEGER,
        activity_name VARCHAR(512),
        remarks TEXT,
        del_flag CHAR(1) DEFAULT '0',
        create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        create_user INTEGER,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        update_user INTEGER
      )`
    ];

    try {
      // 检查并迁移表结构
      for (const sql of createTables) {
        const tableName = sql.match(/CREATE TABLE IF NOT EXISTS (\w+)/)[1];
        console.log('Executing SQL for table:', tableName);
        db.exec(sql);
        log(`Table created or exists: ${tableName}`);

        // 为 sk_user_info 表添加 sort_order 字段（如果不存在）
        if (tableName === 'sk_user_info') {
          try {
            // 检查 sort_order 字段是否已存在
            const columnCheck = db.prepare(`
            SELECT COUNT(*) as count 
            FROM pragma_table_info('sk_user_info') 
            WHERE name = 'sort_order'
          `).get();

            if (columnCheck.count === 0) {
              // 添加 sort_order 字段
              db.exec(`ALTER TABLE sk_user_info ADD COLUMN sort_order INTEGER DEFAULT 0`);
              log('Added sort_order column to sk_user_info table');
            }
          } catch (columnErr) {
            // 字段可能已存在或其他错误
            log(`Error checking/adding sort_order column: ${columnErr.message}`);
          }
        }

        // 为 sk_user_info 初始化管理员用户
        if (tableName === 'sk_user_info') {
          console.log('校验是否存在管理员账号');
          const adminCheck = await getAsync(`SELECT id FROM sk_user_info WHERE phone = 'admin' AND del_flag = '0'`);
          console.log('查询是否存在admin', adminCheck)
          if (!adminCheck) {
            console.log('创建管理员账号');
            try {
              const md5 = require('md5');
              const adminPassword = md5('Sk!3579-');
              await retryOperation(() => {
                const stmt = db.prepare(
                  `INSERT INTO sk_user_info (user_name, phone, password , create_user)
                   VALUES (?, ?, ?, ?)`
                );
                stmt.run('管理员', 'admin', adminPassword, 1);
              });
              log('成功初始化管理员账号');
            } catch (err) {
              log(`初始化管理员账号失败: ${err.message}`);
              throw err;
            }
          }
        }
      }
      log('所有表结构初始化成功');
      return Promise.resolve();
    } catch (err) {
      log(`表结构初始化失败: ${err.message}`);
      throw err;
    }
  }

  // 导出模块
  module.exports = { db, runAsync, allAsync, getAsync, retryOperation, log, initializeTables };
} catch (err) {
  console.error('数据库配置初始化错误:', err.stack);
  process.exit(1);
}